﻿using FreeSql;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using shop.Model;
using shop.Web.Excel;
using shp.Web.Controllers.FileServ;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using CommonTools;
using ExcelHelper = shop.Web.Excel.ExcelHelper;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using unvell.ReoGrid;


namespace shop.Web.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class YingdaoController : ControllerBase
    {
        private readonly IFreeSql _sql;

        public YingdaoController(IFreeSql sql) 
        { 
            _sql = sql;
        }

        [HttpGet]
        public async Task<Result> SetRefundPic(string refundNo, string pic)
        {
            var time = DateTime.Today;

            var find = await _sql.Select<RefundDoc>()
                .Where(a => a.RefundNo == refundNo && a.isdel == false)
                .Where(a => a.FTime >= time)
                .ToOneAsync(a => new { a.Id, a.PicFile});

            await _sql.Update<RefundDoc>()
                .Where(a=> a.isdel== false && a.RefundNo== refundNo)    
                .Set(a=> a.PicFile, pic)
                .ExecuteAffrowsAsync();


            //判断单号文件名之前是否保存过, 如果保存过应该是查询完了,返回false
            return new Result( string.IsNullOrEmpty(find?.PicFile));
        }


        /// <summary> 查询售后数据配置 </summary>
        [HttpGet]
        public async Task<dynamic> GetQueryConfig(string flag ="sh")
        {

            var last = await _sql.Select<TradeSyncLog>()
                .Where(a => a.Flag == flag)
                .OrderByDescending(a => a.SyncEnd)
                .ToOneAsync();

            if (last == null)
            {
                last = new TradeSyncLog()
                {
                    SyncBegin = new DateTime(2025, 4, 1),
                    SyncEnd = new DateTime(2025, 4, 1),
                    Flag = flag,
                };

                await _sql.Insert(last).ExecuteAffrowsAsync();
            }

            if (last.SyncEnd.HasValue && last.SyncEnd.Value >= DateTime.Now.AddMinutes(-10) )
            {
                //不能超过当前10分钟之前
                return new
                {
                    beginDate = string.Empty,
                    endDate = string.Empty,
                };
            }

            int mins = -5;

            DateTime nextTime;
            if(last.SyncEnd.Value < DateTime.Today)
            {
                //今天之前
                nextTime = DateTime.Today;
                if (nextTime > DateTime.Now.AddMinutes(mins))
                {
                    nextTime = DateTime.Now.AddMinutes(mins);
                }

                ////只要今天的 
                //last.SyncEnd = DateTime.Today;
                //nextTime= DateTime.Now.AddMinutes(mins);
            }
            else if( last.SyncEnd.Value < DateTime.Now.AddMinutes(mins))
            {
                // 30分钟之前的, 查到30分钟之前
                nextTime = DateTime.Now.AddMinutes(mins);
            }
            else 
            {
                // 30分钟内的, 查到10分钟之前
                //nextTime = DateTime.Now.AddMinutes(mins0);
                return new
                {
                    beginDate = string.Empty,
                    endDate = string.Empty,
                };
            }

            return new
            {
                beginDate = last.SyncEnd.ToDateStr("yyyy-MM-dd HH:mm:ss"),
                endDate = nextTime.ToString("yyyy-MM-dd HH:mm:ss"),
            };
        }


        /// <summary> 丢失的订单 </summary>
        [HttpGet]
        public async Task<List<string>> GetLostTrades()
        {
            //var begin = new DateTime(2025, 2, 1);
            var begin = DateTime.Today.AddMonths(-6);


            var list = await _sql.Select<OutStockItem, tradeItem>()
                .LeftJoin((a, b) => a.trade_no == b.trade_no)
                .Where((a, b) => SqlExt.EqualIsNull(b.id))
                .Where((a,b)=> a.consign_time>= begin)
                .Take(2000)
                .ToListAsync((a, b) => a.src_tid);

            return list;
        }




        /// <summary> 从excel 读取售后数据 </summary>
        [HttpGet]
        public async Task ReadShouhouDataFromExcel(string excel, string begin, string end)
        {
            var path = Path.Combine(FileTransferController._fileDir, "shouhou", excel);
            if(!System.IO.File.Exists(path))
            {
                return;
            }

            var dt = ExcelHelper.Import(path);

            // 退换补发单号	网店售后单号	网店订单号	退货数量	物流单号(退)	处理状态5	收货状态	登记时间7	审核时间8	申请时间9	退换原因	
            // 退款描述	收货仓库(退)	退货金额	发货金额	应退合计	售后销售单号	责任人	结算方式	收款账号	客户账号	客户编号	收货人	手机	补发类型	发货仓库(原)	货品摘要	客户备注	客服备注	标记


            var time = await _sql.GetTimeAsync();
            var batch = Guid.NewGuid().ToString();


            var list = dt.AsEnumerable()
                .Select(a => new RefundDoc()
                {
                    RefundNo= a.Field<string>(0),
                    ShouhouNo= a.Field<string>(1),
                    trade_no= a.Field<string>(2),
                    RefundQty = a.Field<string>(3).IsNull(0m),
                    ProcessStatus= a.Field<string>(5),
                    CreatedTime= a.Field<string>(7).IsNullDate(null),
                    RefundTime = a.Field<string>(9).IsNullDate(null),
                    RefundReason= a.Field<string>(10),
                    Remarks = a.Field<string>(11),
                    //RefundAmount = a.Field<string>(13).IsNull(0m),
                    //TradeAmount = a.Field<string>(14).IsNull(0m),
                    //TradeAmount = a.Field<string>(14).IsNull(0m),
                    RefundType = a.Field<string>(18),
                    Stock= a.Field<string>(25),
                    logistics_no = a.Field<string>(4),
                    Batch = batch,
                    FTime= time,
                    DocFlag= "sh",
                    logistics_name= a.Field<string>(29),
                })
                .ToList();

            if(list.Count> 0)
            {

                //如果已经发送消息了, 这里标记一下
                var tmp = list.Select(a => new StrTemp(a.RefundNo)).ToList();

                var old2 = _sql.Select<RefundDoc>()
                    .Where(a => a.isdel == false && a.IsSendMessage == true)
                    .FromQuery(_sql.Select<StrTemp>().WithMemory(tmp))
                    .InnerJoin(((a, b) => a.RefundNo == b.Code))
                    .ToList((a, b) => new { a.Id, a.RefundNo });

                foreach (var item in old2)
                {
                    foreach (var it in list.Where(a => a.RefundNo == item.RefundNo))
                    {
                        it.IsSendMessage = true;
                    }
                }

                //
                await _sql.Insert(list).ExecuteAffrowsAsync();

                // 第二个表
                // 货品退换补发单号	货品退换原因	货品编号	货品名称	规格	单位	实退数量	已结算数量	销售单价	货品退货数量	退货优惠(折扣)	货品退货金额	发货数量	发货优惠(折扣)	货品发货金额	应退金额	备注	存货仓库


                var dt2 = ExcelHelper.Import(path, "1");

                var list2 = dt2.AsEnumerable()
                    .Select(a => new RefundDocItem()
                    {
                        RefundNo = a.Field<string>(0),
                        goods_no = a.Field<string>(2),  //货品编号
                        goodsName = a.Field<string>(3),
                        specName = a.Field<string>(4),
                        num = a.Field<string>(6).IsNull(0m),
                        SettleNum = a.Field<string>(7).IsNull(0m),
                        price = a.Field<string>(8).IsNull(0m),
                        RefundQty = a.Field<string>(9).IsNull(0m),
                        Batch = batch,
                        FTime = time,
                    })
                    .ToList();



                await _sql.Insert(list2).ExecuteAffrowsAsync();



                #region 检查重复的

                await _sql.Select<RefundDoc>()
                  .FromQuery(_sql.Select<RefundDoc>()
                      .Where(a => a.isdel == false)
                      .GroupBy(a => a.RefundNo)
                      .Having(a => a.Count() > 1)
                      .WithTempQuery(a => new { a.Key, maxid = a.Max(a.Value.Id) })
                  )
                  .InnerJoin((a, b) => a.RefundNo == b.Key && a.Id != b.maxid)
                  .ToUpdate()
                  .Where(a => a.isdel == false)
                  .Set(a => a.isdel, true)
                  .ExecuteAffrowsAsync();



                // 第二个表
                var tmp2 = list2.Select(a => new StrTemp2(a.Batch, a.RefundNo))
                    .Distinct()
                    .ToList();

                await _sql.Select<RefundDocItem>()
                  .FromQuery(_sql.Select<StrTemp2>()
                      .WithMemory(tmp2)
                  )
                  .InnerJoin((a, b) => a.RefundNo == b.Name && a.Batch != b.Code)
                  .ToDelete()
                  .ExecuteAffrowsAsync();


                #endregion

            }




            // 记录
            var log = new TradeSyncLog()
            {
                SyncBegin = DateTime.Parse(begin),
                SyncEnd = DateTime.Parse(end),
                Flag = "sh",
            };

            await _sql.Insert(log).ExecuteAffrowsAsync();


            // 删除源文件
            System.IO.File.Delete(path);

        }





        /// <summary> 从excel 销售单数据 第二种方式, 合并明细的表格  </summary>
        [HttpGet]
        public async Task ReadOrderDataFromExcel2(string excel )
        {
            var log = NLog.LogManager.GetLogger(nameof(YingdaoController));
            log.Info("ReadOrderDataFromExcel 读取文件: " + excel);

            var path = Path.Combine(FileTransferController._fileDir, "shouhou", excel);
            if (!System.IO.File.Exists(path))
            {
                path = excel.Trim('[', ']','\'').Replace("\\\\","\\");

                if (!System.IO.File.Exists(path))
                {
                    log.Error("找不到文件: " + path);
                    return;
                }
            }

            var dt = ExcelHelper.Import(path);

   

            //var time = await _sql.GetTimeAsync();
            //var batch = Guid.NewGuid().ToString();

            // 物流公司 物流单号
            int nameCol = -1, noCol = -1;
            int i = 0;
            foreach (DataColumn col  in dt.Columns)
            {
                if(col.ColumnName == "物流公司")
                {
                    nameCol = col.Ordinal;
                }
                if (col.ColumnName == "物流单号")
                {
                    noCol = col.Ordinal;
                }

                i++;
            }


            var list = dt.AsEnumerable()
                .Select(a => new 
                {
                    logistics_no = a.Field<string>(noCol),
                    logistics_name = string.IsNullOrEmpty( a.Field<string>(nameCol)) ? "_" : a.Field<string>(nameCol),
                })
                //.Where(a=> !string.IsNullOrEmpty(a.logistics_name) && !string.IsNullOrEmpty(a.logistics_no))
                .Where(a => !string.IsNullOrEmpty(a.logistics_no))
                .ToList();

            if (list.Count == 0)
            {
                return;
            }


            // 修改
            foreach (var item in list)
            {
                await _sql.Update<RefundDoc>()
                    .Where(a => a.logistics_no == item.logistics_no && a.isdel== false && a.DocFlag=="tk")
                    .Set(a => a.logistics_name, item.logistics_name)
                    .ExecuteAffrowsAsync();
            }
            

            // 删除源文件
            System.IO.File.Delete(path);

        }


        /// <summary> 从excel 销售单数据 </summary>
        [HttpGet]
        public async Task ReadOrderDataFromExcel(string excel)
        {
            var log = NLog.LogManager.GetLogger(nameof(YingdaoController));
            log.Info("ReadOrderDataFromExcel 读取文件: " + excel);

            var path = Path.Combine(FileTransferController._fileDir, "shouhou", excel);
            if (!System.IO.File.Exists(path))
            {
                path = excel.Trim('[', ']', '\'').Replace("\\\\", "\\");

                if (!System.IO.File.Exists(path))
                {
                    log.Error("找不到文件: " + path);
                    return;
                }
            }

            var dt = ExcelHelper.Import(path);



            //var time = await _sql.GetTimeAsync();
            //var batch = Guid.NewGuid().ToString();

            // 物流公司 物流单号
            int nameCol = -1, noCol = -1;
            int i = 0;
            foreach (DataColumn col in dt.Columns)
            {
                if (col.ColumnName == "物流公司")
                {
                    nameCol = col.Ordinal;
                }
                if (col.ColumnName == "物流单号")
                {
                    noCol = col.Ordinal;
                }

                i++;
            }


            var list = dt.AsEnumerable()
                .Select(a => new
                {
                    logistics_no = a.Field<string>(noCol),
                    logistics_name = string.IsNullOrEmpty(a.Field<string>(nameCol)) ? "_" : a.Field<string>(nameCol),
                })
                //.Where(a=> !string.IsNullOrEmpty(a.logistics_name) && !string.IsNullOrEmpty(a.logistics_no))
                .Where(a => !string.IsNullOrEmpty(a.logistics_no))
                .ToList();

            if (list.Count == 0)
            {
                return;
            }


            // 修改
            foreach (var item in list)
            {
                await _sql.Update<RefundDoc>()
                    .Where(a => a.logistics_no == item.logistics_no && a.isdel == false && a.DocFlag == "tk")
                    .Set(a => a.logistics_name, item.logistics_name)
                    .ExecuteAffrowsAsync();
            }


            // 删除源文件
            System.IO.File.Delete(path);

        }





        /// <summary> 从excel 读取补偿数据 </summary>
        [HttpGet]
        public async Task ReadBuchangDataFromExcel(string excel, string begin, string end)
        {
            var path = Path.Combine(FileTransferController._fileDir, "shouhou", excel);
            if (!System.IO.File.Exists(path))
            {
                return;
            }

            var dt = ExcelHelper.Import(path);

            //物流单号   标记 补偿退款单号  网店售后单号 网店订单号   处理状态 登记时间    审核时间 申请时间    审核人 支付时间    退款原因 应退合计    结算方式 收款人账户类型 户名 收款账号    客户账号 客户编号    售后销售单号 货品摘要    手机 责任人 订单编号   客户备注    客服备注 发货仓库(原) 附件图片
            //物流单号(原) 标记 补偿退款单号  网店售后单号 网店订单号   处理状态 登记时间    审核时间 申请时间    审核人 支付时间    退款原因 应退合计    结算方式 收款人账户类型 户名 收款账号    客户账号 客户编号    售后销售单号 货品摘要    手机 责任人 订单编号 客户备注    客服备注 发货仓库(原) 附件图片


            var time = await _sql.GetTimeAsync();
            var batch = Guid.NewGuid().ToString();


            var list = dt.AsEnumerable()
                .Select(a => new RefundDoc()
                {
                    logistics_no = a.Field<string>(0),
                    RefundNo = a.Field<string>(2),
                    src_tids = a.Field<string>(23),
                    trade_no= a.Field<string>(4),  //网店订单号
                    //RefundQty = a.Field<string>(33).IsNull(0m),
                    ProcessStatus = a.Field<string>(5), //状态
                    CreatedTime = a.Field<string>(6).IsNullDate(null),
                    RefundTime = a.Field<string>(8).IsNullDate(null), //申请时间
                    //goo
                    RefundReason = a.Field<string>(11),
                    Remarks = a.Field<string>(20),
                    Stock = a.Field<string>(26),

                    Batch = batch,
                    FTime = time,
                    DocFlag= "tk",
                    logistics_name=a.Field<string>(28),
                })
                //.Where(a=> a.ProcessStatus != "已取消")
                .ToList();



            if (list.Count > 0)
            {

                //如果已经发送消息了, 这里标记一下
                var tmp = list.Select(a => new StrTemp(a.RefundNo)).ToList();

                var old2 = _sql.Select<RefundDoc>()
                    .Where(a => a.isdel == false && a.IsSendMessage == true)
                    .FromQuery(_sql.Select<StrTemp>().WithMemory(tmp))
                    .InnerJoin(((a, b) => a.RefundNo == b.Code))
                    .ToList((a, b) => new { a.Id, a.RefundNo });

                foreach (var item in old2)
                {
                    foreach (var it in list.Where(a => a.RefundNo == item.RefundNo))
                    {
                        it.IsSendMessage = true;
                    }
                }


                await _sql.Insert(list).ExecuteAffrowsAsync();

                // 第二个表
                // 货品补偿退款单号	货品退款原因	货品编号	货品名称	规格	单位	数量	销售单价	退款金额	备注


                var dt2 = ExcelHelper.Import(path, "1");

                var list2 = dt2.AsEnumerable()
                    .Select(a => new RefundDocItem()
                    {
                        RefundNo = a.Field<string>(0),
                        GoodsReason = a.Field<string>(1),
                        goods_no = a.Field<string>(2),  //货品编号
                        goodsName = a.Field<string>(3),
                        specName = a.Field<string>(4),
                        num = a.Field<string>(6).IsNull(0m),
                        //SettleNum = a.Field<string>(7).IsNull(0m),
                        price = a.Field<string>(7).IsNull(0m),
                        total_amount = a.Field<string>(8).IsNull(0m),
                        //RefundQty = a.Field<string>(9).IsNull(0m),
                        remarks = a.Field<string>(9),
                        Batch = batch,
                        FTime = time,
                    })
                    .ToList();



                await _sql.Insert(list2).ExecuteAffrowsAsync();


                #region 检查重复的

                await _sql.Select<RefundDoc>()
                  .FromQuery(_sql.Select<RefundDoc>()
                      .Where(a => a.isdel == false)
                      .GroupBy(a => a.RefundNo)
                      .Having(a => a.Count() > 1)
                      .WithTempQuery(a => new { a.Key, maxid = a.Max(a.Value.Id) })
                  )
                  .InnerJoin((a, b) => a.RefundNo == b.Key && a.Id != b.maxid)
                  .ToUpdate()
                  .Set(a => a.isdel, true)
                  .Where(a => a.isdel == false)
                  .ExecuteAffrowsAsync();


                // 第二个表
                var tmp2 = list2.Select(a => new StrTemp2(a.Batch, a.RefundNo))
                    .Distinct()
                    .ToList();

                await _sql.Select<RefundDocItem>()
                  .FromQuery(_sql.Select<StrTemp2>()
                      .WithMemory(tmp2)
                  )
                  .InnerJoin((a, b) => a.RefundNo == b.Name && a.Batch != b.Code)
                  .ToDelete()
                  .ExecuteAffrowsAsync();

                #endregion

            }



            // 记录
            var log = new TradeSyncLog()
            {
                SyncBegin = DateTime.Parse(begin),
                SyncEnd = DateTime.Parse(end),
                Flag = "tk",
            };

            await _sql.Insert(log).ExecuteAffrowsAsync();


            // 删除源文件
            System.IO.File.Delete(path);

        }



        /// <summary> 从excel 读取补偿数据 </summary>
        [HttpGet]
        public async Task ReadBuchangPicFromExcel(string excel, string begin, string end)
        {
            var path = Path.Combine(FileTransferController._fileDir, "shouhou", excel);
            if (!System.IO.File.Exists(path))
            {
                return;
            }


            using (FileStream file = new FileStream(excel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (ExcelPackage pck = new ExcelPackage(file))
                {
                    ExcelWorkbook workbook = pck.Workbook;

                    if (workbook == null || workbook.Worksheets.Count == 0)
                    {
                        return ;  //没有表格
                    }

                    var sheet = workbook.Worksheets.FirstOrDefault();

                    //这里开始读取文件
                    //return WorksheetToTable(sheet, titleRow, dataBegin, includeBlankRow);
                    int rows = (sheet.Dimension?.End?.Row ?? 0);
                    //找到列
                    int cols = Math.Min(50, sheet.Dimension?.End?.Column ?? 0);
                    int numCol = -1, picCol =-1;
                    var titleRow = 1;
                    for (int i = 1; i <= cols; i++)
                    {
                        string name = sheet.Cells[titleRow, i].Value?.ToString();
                        if(name== "补偿退款单号")
                        {
                            numCol = i;
                        }
                        if (name == "附件图片")
                        {
                            picCol= i;
                        }
                    }

                    var arr = new List<(string, string)>();

                    //for (int i = 2; i <= rows; i++)
                    //{
                    //    //处理这一行的图片
                        
                    //    arr.Add((sheet.Cells[i, numCol].Value?.ToString(), ""));
                    //}

                    var dir = Path.Combine( FileTransferController._fileDir,"refund_excel_images");
                    var di = new DirectoryInfo(dir);
                    if (!di.Exists)
                    {
                        di.Create();
                    }

                    foreach (var item in sheet.Drawings)
                    {
                        if (item is ExcelPicture picture)
                        {
                            var row = item.From.Row;  //这个行号从0开始
                            var image = picture.Image;
                            var imageBytes = picture.Image.ImageBytes;

                            // 生成保存路径
                            var num = sheet.Cells[row+1, numCol].Value?.ToString();

                            var imageName =Path.Combine("refund_excel_images", $"{num}_{picture.Name.Replace(" ",string.Empty)}.{picture.Image.Type.ToString().ToLower()}");
                            var savePath = Path.Combine(FileTransferController._fileDir, imageName);

                            System.IO.File.WriteAllBytes(savePath, imageBytes);

                            await _sql.Update<RefundDoc>()
                                .Where(a => a.RefundNo == num && a.isdel == false)
                                .Set(a => a.PicFile, imageName)
                                .ExecuteAffrowsAsync();
                        }
                    }

                }
            }

        }

        /// <summary> 需要发微信的内容 </summary>
        [HttpGet]
        public async Task<List<string[]>> GetSendWechatList()
        {
            /*
             *  SELECT c.DocFlag, c.id, c.RefundNo, isnull(c.logistics_no,'') LogiNo, c.Stock, d.goods_no, c.trade_no, c.RefundQty, c.RefundAmount,
        ISNULL(b.WechatGroup,'') WechatGroup , c.PicFile, c.RefundReason
        FROM dbo.RefundDoc AS c 
        INNER JOIN dbo.RefundDocItem AS d ON c.RefundNo = d.RefundNo AND c.Batch=d.Batch 
        INNER JOIN dbo.StockWechat AS b ON c.Stock= b.Stock AND b.isdel =0 
            AND (CASE   WHEN c.DocFlag ='sh' and (b.Flag='工厂' OR ISNULL(b.Flag,'') ='' OR b.WechatGroup LIKE '%工厂%') THEN 1 
                        WHEN c.DocFlag='tk' AND c.RefundReason LIKE '%拦截%' AND (b.Flag='快递' OR ISNULL(b.Flag,'') ='' OR b.WechatGroup LIKE '%快递%')  THEN 1 
                        WHEN c.DocFlag='tk' AND c.RefundReason LIKE '%厂家%' AND  (b.Flag='工厂' OR ISNULL(b.Flag,'') ='' OR b.WechatGroup LIKE '%工厂%') THEN 1 
                        ELSE 0 END ) =1
        where c.Isdel =0 AND ISNULL(IsSendMessage,0) =0 and c.Stock<>''
        AND ((c.DocFlag='sh' AND c.logistics_no<>'' ) OR (c.DocFlag='tk' AND (c.RefundReason like '%拦截%' OR c.RefundReason like '%厂家%'  )) )
        ORDER BY b.WechatGroup
            */

            var minDate = DateTime.Today.AddDays(-5);

            var list = await _sql.Select<RefundDoc, RefundDocItem>()
                .InnerJoin((a, b) => a.RefundNo == b.RefundNo && a.Batch == b.Batch)
                .Where((a, b) => a.isdel == false && a.IsSendMessage == false && string.IsNullOrEmpty(a.Stock) == false && a.Stock!= "阿里代拍")
                .Where((a, b) => (a.DocFlag == "sh" && string.IsNullOrEmpty(a.logistics_no) == false) || (a.DocFlag == "tk" && (a.RefundReason.Contains("拦截") || a.RefundReason.Contains("厂家"))))
                .Where((a,b) => a.ProcessStatus !="已取消" && a.FTime>=minDate) 
                //.OrderBy((a,b)=> a)
                .ToListAsync((a,b)=> new {a.Id, a.RefundNo, a.trade_no, a.DocFlag, a.logistics_no,a.logistics_name, a.Stock, b.goodsName, b.goods_no, a.RefundQty, a.RefundAmount,a.PicFile, a.RefundReason });


            var wc = await _sql.Select<StockWechat>()
                .Where(a => a.isdel == false)
                .ToListAsync();

            //
            var data = list
                //.OrderBy(a=> a.Stock)
                .Select(a =>
            {
                var find = wc.Where(c => c.Stock == a.Stock);
                if((a.Stock== "飞燕仓" || a.Stock.Contains( "沃兴") || a.Stock?.Contains("鸣飞")== true) && a.DocFlag=="tk" && a.RefundReason.Contains("快递") )
                {

                }
                if (a.DocFlag == "sh")
                {
                    find = find.Where(c => c.Flag == "工厂" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("工厂"));
                }
                else if (a.DocFlag == "tk" && a.RefundReason?.Contains("拦截") == true)
                {
                    find = find.Where(c => c.Flag == "快递" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("快递"));
                }
                else if (a.DocFlag == "tk" && a.RefundReason?.Contains("厂家") == true)
                {
                    find = find.Where(c => c.Flag == "工厂" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("工厂"));
                }

                //var findList = find.Where(c => !string.IsNullOrEmpty(c.LogiName) && a.logistics_name?.Contains(c.LogiName) == true).ToList();  //限制快递公司
                var findList = a.DocFlag=="tk" 
                    ? find.ToList()   //补偿退款不限制
                    : find.Where(c => !string.IsNullOrEmpty(c.LogiName) && a.logistics_name?.Contains(c.LogiName) == true).ToList();  //限制快递公司

                if (findList.Count == 0)
                {
                    //不限制快递公司
                    findList = find.Where(c => string.IsNullOrEmpty(c.LogiName)).ToList();
                    if (findList.Count == 0)
                    {
                        return null;
                    }
                }
                else if(findList.Count >1 && a.DocFlag=="tk")
                {
                    findList =  findList.Where(c => !string.IsNullOrEmpty(c.LogiName) && a.logistics_name?.Contains(c.LogiName) == true).ToList();  //限制快递公司
                }

                if (findList.Count == 0)
                {
                    return null;
                }


                string text = string.Empty;

                if (a.DocFlag == "sh")
                {
                    text = "订单号 " + a.trade_no + "\n退货单号 " + a.logistics_no + "\n型号 " + a.goods_no + "\n退货数量 " + a.RefundQty.ToString("#0.##");
                }
                else if (a.RefundReason?.Contains("拦截") == true)
                {
                    text = a.logistics_no + "拦截";
                }
                else
                {
                    text = "订单号 " + a.trade_no + "\n发货单号 " + a.logistics_no + "\n型号 " + a.goods_no;
                }

                return new[] { text, findList[0].WechatGroup, a.PicFile ?? string.Empty, a.Id.ToString() };
            })
            .Where(a=> a != null)
            .OrderBy(a=> a[1])
            .ToList();


            return data;
        }


        [HttpGet]
        public async Task<List<string[]>> GetSendWechatListTest()
        {
        

            var list = await _sql.Select<RefundDoc, RefundDocItem>()
                .InnerJoin((a, b) => a.RefundNo == b.RefundNo && a.Batch == b.Batch)
                .Where((a, b) => a.isdel == false  )
                .Where((a, b) => (a.DocFlag == "sh" && string.IsNullOrEmpty(a.logistics_no) == false)
                || (a.DocFlag == "tk" && (a.RefundReason.Contains("拦截") || a.RefundReason.Contains("厂家"))))
                .Where((a, b) => a.logistics_no == "YT8782884536321")
                .Where((a, b) => a.ProcessStatus != "已取消")
                .OrderBy((a, b) => a.Id)
                .ToListAsync((a, b) => new { a.Id, a.RefundNo, a.trade_no, a.DocFlag, a.logistics_no, a.Stock, b.goodsName, b.goods_no, a.RefundQty, a.RefundAmount, a.PicFile, a.RefundReason, a.logistics_name });


            var wc = await _sql.Select<StockWechat>()
                .Where(a => a.isdel == false)
                .ToListAsync();

            //
            var data = list.Select(a =>
            {
                var find = wc.Where(c => c.Stock == a.Stock);

                if (a.DocFlag == "sh")
                {
                    find = find.Where(c => c.Flag == "工厂" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("工厂"));
                }
                else if (a.DocFlag == "tk" && a.RefundReason?.Contains("拦截") == true)
                {
                    find = find.Where(c => c.Flag == "快递" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("快递"));
                }
                else if (a.DocFlag == "tk" && a.RefundReason?.Contains("厂家") == true)
                {
                    find = find.Where(c => c.Flag == "工厂" || string.IsNullOrEmpty(c.Flag) || c.WechatGroup.Contains("工厂"));
                }
                //else
                //{
                //    //find = find;
                //    //return null;
                //}

                var findList = find.Where(c => !string.IsNullOrEmpty(c.LogiName) && a.logistics_name?.Contains(c.LogiName) == true).ToList();  //限制快递公司

                if (findList.Count == 0)
                {
                    //不限制快递公司
                    findList = find.Where(c => string.IsNullOrEmpty(c.LogiName)).ToList();
                    if (findList.Count == 0)
                    {
                        return null;
                    }
                }

                if (findList.Count == 0)
                {
                    return null;
                }


                string text = string.Empty;

                if (a.DocFlag == "sh")
                {
                    text = "订单号 " + a.trade_no + "\n退货单号 " + a.logistics_no + "\n型号 " + a.goods_no + "\n退货数量 " + a.RefundQty.ToString("#0.##");
                }
                else if (a.RefundReason?.Contains("拦截") == true)
                {
                    text = a.logistics_no + "拦截";
                }
                else
                {
                    text = "订单号 " + a.trade_no + "\n发货单号 " + a.logistics_no + "\n型号 " + a.goods_no;
                }

                return new[] { text, findList[0].WechatGroup, a.PicFile ?? string.Empty, a.Id.ToString() };
            })
            .Where(a => a != null)
            .ToList();


            return data;
        }



        /// <summary> 需要查询物流单号公司的 </summary>
        [HttpGet]
        public async Task<string> GetNeedLogiName()
        {
            var begin = DateTime.Today.AddDays(-7);

            var list = await _sql.Select<RefundDoc>()
                .Where(a => a.isdel == false && a.IsSendMessage == false 
                    && a.DocFlag =="tk"
                    && a.FTime>= begin
                    //&& string.IsNullOrEmpty(a.logistics_name) && a.logistics_no.Length > 2)
                    && string.IsNullOrEmpty(a.logistics_name))
                .ToListAsync(a => a.trade_no);

            return string.Join(',', list);
        }

    }
}
